ALTER PROCEDURE [SP_CREATE_UPDATE_PRODUCT_INFO_BY_FG_PART] 
(
	@FAB_PART		VARCHAR(50),
	@FG_PRODUCT		VARCHAR(50),
	@MRKT_PART		VARCHAR(50),
    @PLANNER		VARCHAR(20),
	@DIES_PER_PKG	INT,
	@BOM_PART		VARCHAR(50),
	@BOM_QTY		INT,
	@PART_STATUS	VARCHAR(50),
	@CHIP_FAMILY	VARCHAR(50),
	@PORT			INT,
	@CHIPSET		VARCHAR(50),
    @SORT_VENDOR1   VARCHAR(50),
	@SORT_VENDOR2   VARCHAR(50),
	@PACKAGE		VARCHAR(20),
	@PACKAGE_SIZE	VARCHAR(50),
	@PIN_COUNT		INT,
	@WIRE_PKG_BALL_RATIO	VARCHAR(50),
	@SUBTRATE_TECH		VARCHAR(50),
	@SUBTRATE_LAYERS	INT,
	@ASSY_VENDOR1	VARCHAR(50),
	@ASSY_VENDOR2	VARCHAR(50),
    @TESTER_PLATFORM VARCHAR(50),
	@TEST_TIME		  FLOAT,
	@INDEX_TIME		  FLOAT,
	@VENDOR_TESTER_Cost		NUMERIC(18,4),
	@CONSIGNED_TESTER_Cost	NUMERIC(18,4),
	@TEST_VENDOR1	VARCHAR(50),
	@TEST_VENDOR2	VARCHAR(50),
	@FG_VENDOR1	VARCHAR(50),
	@FG_VENDOR2	VARCHAR(50),
	@SORT_COST		NUMERIC(18,4),
	@ASSY_COST		NUMERIC(18,4),
	@TEST_COST		NUMERIC(18,4),
	@EOL_COST		NUMERIC(18,4),
	@GDPW			INT, --@NDPW			INT,
	@SORT_YIELD		NUMERIC(18,4),
	@ASSY_YIELD		NUMERIC(18,4),
	@FT_YIELD		NUMERIC(18,4),	
	@FAB_CycleTime	FLOAT,
	@SORT_CycleTime FLOAT,
	@ASSY_CycleTime FLOAT,
	@TEST_CycleTime FLOAT,
	@EOL_CycleTime	FLOAT,
	@UNITS_PERTRAY	INT,
	@INNER_BOX_QTY	INT,
	@OUTER_BOX_QTY  INT,
	@WEIGHT_PER_BOX FLOAT,
	@ECCN			VARCHAR(50),
	@HTSCODE		VARCHAR(50),
	@CCATS			VARCHAR(50),
	@QUARTER		INT,
	@YEAR			INT
)
as
begin
	
	declare @process_step_id int
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @FGPROCESSSTEP_ID INT
	DECLARE @ROWCOUNT	INT 
	DECLARE @YIELD NUMERIC(18,4)
	DECLARE @COST		NUMERIC(18,4)
	DECLARE @VENDOR1	VARCHAR(50)
	DECLARE @VENDOR2	VARCHAr(50)
	DECLARE @LOCATION_ID1 INT
	DECLARE @LOCATION_ID2 INT	

	SELECT @FGPROCESSSTEP_ID = PROCESS_STEP_ID FROM FN_GET_PROCESS_STEP_ID(@FG_PRODUCT,'Finished Goods','FG')

	if @FGPROCESSSTEP_ID is null
	BEGIN
			update Client_Table set [NAME] = 'lot_number' where [NAME] = 'lot_number'
			return
	END
	ELSE
	BEGIN
			declare  @psIdCursor cursor
			SET @psIdCursor = CURSOR FOR SELECT processstep_id, PS_Type FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID(@FGPROCESSSTEP_ID, 0) 
			OPEN @psIdCursor
			FETCH NEXT FROM @psIdCursor INTO @process_step_id,@PS_TYPE
			WHILE ( @@FETCH_STATUS = 0 )
			BEGIN								
					IF @PS_TYPE  = 'FAB'
					BEGIN
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Cycle Time' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Standard Cycle Time', 'UNITS', @FAB_CycleTime )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @FAB_CycleTime WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Cycle Time'
							END

							IF @GDPW > 0
							BEGIN
								IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Gross Die/Wafer' ) = 0 )
								BEGIN
										INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
											VALUES (@process_step_id, 'Gross Die/Wafer', 'UNITS', @GDPW )
								END
								ELSE
								BEGIN
										UPDATE PROCESS_PARAMETER SET Parameter_Value = @GDPW WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Gross Die/Wafer'
								END
							END

							/*IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Plan NDW' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Plan NDW', 'UNITS',  @NDPW)
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @NDPW WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Plan NDW'
							END*/
						
					END -- END OF FAB

					IF @PS_TYPE  = 'SORT'
					BEGIN
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Cycle Time' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Standard Cycle Time', 'UNITS', @SORT_CycleTime )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @SORT_CycleTime WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Cycle Time'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Target Yield' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Target Yield', '%', isNull(@SORT_YIELD,0.0) )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = isNull(@SORT_YIELD,0.0) WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Target Yield'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Unit Cost' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Standard Unit Cost', '$',  isNull(@SORT_COST,0.0))
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = isNull(@SORT_COST,0.0) WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Unit Cost'
							END

							SET @YIELD = isNull(@SORT_YIELD,0.0)
							SET @COST  = isNull(@SORT_COST,0.0)	

							SET @VENDOR1 = @SORT_VENDOR1
							SET @VENDOR2 = @SORT_VENDOR2
							
					END -- END OF SORT

					IF @PS_TYPE  = 'Assembly'
					BEGIN

							/*IF @BOM_PART IS NOT NULL AND @BOM_QTY > 0 
							BEGIN
									SELECT @BOM_PS_ID = PROCESSSTEP_ID FROM PRODUCT WHERE PART_NUMBER = 
							END*/
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Cycle Time' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Standard Cycle Time', 'UNITS', @ASSY_CycleTime )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @ASSY_CycleTime WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Cycle Time'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Target Yield' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Target Yield', '%', isNull(@ASSY_YIELD, 0.0) )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = isNull(@ASSY_YIELD, 0.0) WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Target Yield'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Unit Cost' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Standard Unit Cost', '$',  isNull(@ASSY_COST, 0.0))
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = isNull(@ASSY_COST, 0.0) WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Unit Cost'
							END
							
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'PIN' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'PIN', 'UNITS', @PIN_COUNT )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @PIN_COUNT WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'PIN'
							END
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Dies/Package' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Dies/Package', 'UNITS', @DIES_PER_PKG )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @DIES_PER_PKG WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Dies/Package'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Package ID' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Package ID', 'TEXT', @PACKAGE )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @PACKAGE WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Package ID'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Package Size' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Package Size', 'TEXT', @PACKAGE_SIZE )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @PACKAGE_SIZE WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Package Size'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Wire to Package Ball ratio' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Wire to Package Ball ratio', 'TEXT', @WIRE_PKG_BALL_RATIO )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @WIRE_PKG_BALL_RATIO WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Wire to Package Ball ratio'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Substrate Technology' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Substrate Technology', 'TEXT', @SUBTRATE_TECH )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @SUBTRATE_TECH WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Substrate Technology'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Substrate Layer' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Substrate Layer', 'TEXT', @SUBTRATE_LAYERS )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @SUBTRATE_LAYERS WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Substrate Layer'
							END

							SET @YIELD = isNull(@ASSY_YIELD,0.0)
							SET @COST  = isNull(@ASSY_COST,0.0)	
							SET @VENDOR1 = @ASSY_VENDOR1
							SET @VENDOR2 = @ASSY_VENDOR2
					END

					IF @PS_TYPE  = 'FT'
					BEGIN
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Cycle Time' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Standard Cycle Time', 'UNITS', @TEST_CycleTime )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @TEST_CycleTime WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Cycle Time'
							END
							
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Target Yield' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Target Yield', '%', isNull(@FT_YIELD, 0.0) )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = isNull(@FT_YIELD, 0.0) WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Target Yield'
							END
							
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Unit Cost' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Standard Unit Cost', '$',  isNull(@TEST_COST, 0.0))
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = isNull(@TEST_COST, 0.0) WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Unit Cost'
							END
							
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Tester Platform' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Tester Platform', 'TEXT', @TESTER_PLATFORM )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @TESTER_PLATFORM WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Tester Platform'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Test Time' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Test Time', 'TEXT', @TEST_Time )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @TEST_Time WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Test Time'
							END
							
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Index Time' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Index Time', 'TEXT', @INDEX_TIME )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @INDEX_TIME WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Index Time'
							END
							
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Vendor Tester Cost' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Vendor Tester Cost', '$',  isNull(@VENDOR_TESTER_Cost, 0.0))
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = isNull(@VENDOR_TESTER_Cost, 0.0) WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Vendor Tester Cost'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Consigned Tester Cost' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Consigned Unit Cost', '$',  isNull(@CONSIGNED_TESTER_Cost, 0.0))
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = isNull(@CONSIGNED_TESTER_Cost, 0.0) WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Consigned Tester Cost'
							END

							SET @YIELD = isNull(@FT_YIELD,0.0)
							SET @COST  = isNull(@TEST_COST,0.0)	
							SET @VENDOR1 = @TEST_VENDOR1
							SET @VENDOR2 = @TEST_VENDOR2
						
					END

					IF @PS_TYPE  = 'EOL'
					BEGIN
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Cycle Time' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Standard Cycle Time', 'UNITS', @EOL_CycleTime )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @EOL_CycleTime WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Cycle Time'
							END
							
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Unit Cost' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Standard Unit Cost', '$',  isNull(@EOL_COST,0.0))
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = isNull(@EOL_COST,0.0) WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Standard Unit Cost'
							END

							SET @YIELD = 100.0
							SET @COST  = isNull(@EOL_COST,0.0)
					END

					IF @PS_TYPE  = 'FG'
					BEGIN
							UPDATE PRODUCT SET PART_STATUS = @PART_STATUS WHERE PROCESSSTEP_ID = @process_step_id
							
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Planner' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Planner', 'TEXT', @PLANNER )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @PLANNER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Planner'
							END

							/*IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Short Name' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Short Name', 'TEXT', @SHORT_NAME )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @SHORT_NAME WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Short Name'
							END*/

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Chip Family' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Chip Family', 'TEXT', @Chip_Family )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @Chip_Family WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Chip Family'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Chipset1' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Chipset1', 'TEXT', @CHIPSET )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @CHIPSET WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Chipset1'
							END
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'PORT' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'PORT', 'UNITS', @PORT )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @PORT WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'PORT'
							END
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Units Per Tray' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Units Per Tray', 'UNITS', @UNITS_PERTRAY )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @UNITS_PERTRAY WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Units Per Tray'
							END
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Trays Per InnerBox' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Trays Per InnerBox', 'UNITS', @INNER_BOX_QTY )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @INNER_BOX_QTY WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Trays Per InnerBox'
							END
							
							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'InnerBoxes Per OuterBox' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'InnerBoxes Per OuterBox', 'UNITS', @OUTER_BOX_QTY )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @OUTER_BOX_QTY WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'InnerBoxes Per OuterBox'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Wieght per Box' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Wieght per Box', 'TEXT', @WEIGHT_PER_BOX )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @WEIGHT_PER_BOX WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Wieght per Box'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'ECCN' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'ECCN', 'TEXT', @ECCN )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @ECCN WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'ECCN'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Harmonized Traffic Code' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'Harmonized Traffic Code', 'TEXT', @HTSCODE )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @HTSCODE WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'Harmonized Traffic Code'
							END

							IF( ( SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'CCATS' ) = 0 )
							BEGIN
									INSERT INTO PROCESS_PARAMETER (PROCESSSTEP_ID, parameter_key, Parameter_Units, Parameter_Value )
										VALUES (@process_step_id, 'CCATS', 'TEXT', @CCATS )
							END
							ELSE
							BEGIN
									UPDATE PROCESS_PARAMETER SET Parameter_Value = @CCATS WHERE PROCESSSTEP_ID = @process_step_id AND parameter_key = 'CCATS'
							END
							SET @VENDOR1 = @FG_VENDOR1
							SET @VENDOR2 = @FG_VENDOR2
					END -- END OF FG

					--TO UPDATE THE Update_View_Cost_Table 
					IF @PS_TYPE = 'SORT' OR @PS_TYPE = 'ASSEMBLY' OR  @PS_TYPE = 'FT' OR @PS_TYPE = 'EOL'
					BEGIN	
						SELECT @ROWCOUNT = COUNT(*) FROM Update_View_Cost_Table where ProcessStep_ID = @process_step_id AND YEAR(FIN_YEAR) = @Year
						IF ( @ROWCOUNT = 0 )
						BEGIN
							IF @Quarter = 1
							BEGIN
								INSERT INTO Update_View_Cost_Table ( ProcessStep_id, Q1_Standard_Cost,Q1_Yield, Fin_Year, Timestamp) 
									VALUES ( @process_step_id, @COST, @YIELD, '01/01/'+CONVERT(VARCHAR,@Year), CURRENT_TIMESTAMP  )
							END
							ELSE IF @Quarter = 2
							BEGIN
								INSERT INTO Update_View_Cost_Table ( ProcessStep_id, Q2_Standard_Cost,Q2_Yield, Fin_Year, Timestamp) 
									VALUES ( @process_step_id, @COST, @YIELD,  '01/01/'+CONVERT(VARCHAR,@Year), CURRENT_TIMESTAMP  )
							END
							ELSE IF @Quarter = 3
							BEGIN
								INSERT INTO Update_View_Cost_Table ( ProcessStep_id, Q3_Standard_Cost,Q3_Yield, Fin_Year, Timestamp) 
									VALUES ( @process_step_id, @COST, @YIELD,  '01/01/'+CONVERT(VARCHAR,@Year), CURRENT_TIMESTAMP  )
							END
							ELSE IF @Quarter = 4
							BEGIN
								INSERT INTO Update_View_Cost_Table ( ProcessStep_id, Q4_Standard_Cost,Q4_Yield, Fin_Year, Timestamp) 
									VALUES ( @process_step_id, @COST,  @YIELD,  '01/01/'+CONVERT(VARCHAR,@Year), CURRENT_TIMESTAMP  )
							END	
						END
						ELSE
						BEGIN
							IF @Quarter = 1
							BEGIN
								UPDATE Update_View_Cost_Table SET Q1_Standard_Cost = @COST, Q1_Yield= @YIELD, Timestamp = CURRENT_TIMESTAMP WHERE ProcessStep_id = @process_step_id AND YEAR(FIN_Year) = @Year
							END
							ELSE IF @Quarter = 2
							BEGIN
								UPDATE Update_View_Cost_Table SET Q2_Standard_Cost = @COST, Q2_Yield= @YIELD, Timestamp = CURRENT_TIMESTAMP WHERE ProcessStep_id = @process_step_id AND YEAR(FIN_Year) = @Year
							END
							ELSE IF @Quarter = 3
							BEGIN
								UPDATE Update_View_Cost_Table SET Q3_Standard_Cost = @COST, Q3_Yield= @YIELD, Timestamp = CURRENT_TIMESTAMP WHERE ProcessStep_id = @process_step_id AND YEAR(FIN_Year) = @Year
							END
							ELSE IF @Quarter = 4
							BEGIN
								UPDATE Update_View_Cost_Table SET Q4_Standard_Cost = @COST, Q4_Yield= @YIELD, Timestamp = CURRENT_TIMESTAMP WHERE ProcessStep_id = @process_step_id AND YEAR(FIN_Year) = @Year
							END	
						END--END OF ROW COUNT	
					END-- COST UPDATE

					-- TO UPDATE PROCESS_STEP_LOCATION_TABLE
					IF @PS_TYPE = 'SORT' OR @PS_TYPE = 'ASSEMBLY' OR  @PS_TYPE = 'FT' OR @PS_TYPE = 'FG'
					BEGIN
						SELECT @LOCATION_ID1 = LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = @VENDOR1 AND L_TYPE = @PS_TYPE
						IF @LOCATION_ID1 IS NOT NULL
						BEGIN
								IF ( (SELECT COUNT(*) FROM PROCESS_STEP_LOCATION_TABLE WHERE Location_Id = @LOCATION_ID1 AND PROCESSSTEP_ID =  @process_step_id ) = 0 )
								BEGIN
									 INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
										VALUES( @process_step_id, @LOCATION_ID1, CURRENT_TIMESTAMP )
								END
						END	
						SELECT @LOCATION_ID2 = LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = @VENDOR2 AND L_TYPE = @PS_TYPE
						IF @LOCATION_ID2 IS NOT NULL
						BEGIN
								IF ( (SELECT COUNT(*) FROM PROCESS_STEP_LOCATION_TABLE WHERE Location_Id = @LOCATION_ID2 AND PROCESSSTEP_ID =  @process_step_id ) = 0 )
								BEGIN
									 INSERT INTO Process_Step_Location_Table(ProcessStep_ID, Location_ID, PSLT_Timestamp) 
										VALUES( @process_step_id, @LOCATION_ID2, CURRENT_TIMESTAMP )
								END
						END	
					END
					SET @VENDOR1 = NULL
					SET @VENDOR2 = NULL
					SET @COST = NULL
					SET @YIELD = NULL				 
					SET @PS_TYPE = null
					SET @process_step_id = NULL
					
			FETCH NEXT FROM @psIdCursor INTO @process_step_id, @PS_TYPE
			END
			CLOSE @psIdCursor
			DEALLOCATE @psIdCursor
	END
	

end